package secondriver.springsubway.scaffold;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * Page class aim at solving some query paging problem.
 */
public final class Page{

	private int totals;

	private int pages;

	private int currentIndex = 1;

	private int pageSize = 1;

	private PagingSql pagingSql;

	private ComputeCount computeCount;

	private String sql;

	@Deprecated
	public static Page build(PagingSql pagingSql, String sql, int totals,
	        int pageSize) {
		return new Page(pagingSql, null, sql, totals, pageSize);
	}

	public static Page build(PagingSql pagingSql, ComputeCount computeCount,
	        String sql, int pageSize) {
		if (null == computeCount) {
			throw new IllegalArgumentException(
			        "Page build ComputeCount implement instance must not be null.");
		}
		return new Page(pagingSql, computeCount, sql, pageSize);
	}

	public static Page build(PagingSql pagingSql, Connection conn, String sql,
	        int pageSize) {
		if (null == conn) {
			throw new IllegalArgumentException(
			        "Page build connection instance must not be null.");
		}
		InnerComputeCount computeCount = new InnerComputeCount(conn);
		return new Page(pagingSql, computeCount, sql, pageSize);
	}

	private Page(PagingSql pagingSql, ComputeCount computeCount, String sql,
	        int pageSize) {
		this(pagingSql, computeCount, sql, 0, pageSize);
	}

	private Page(PagingSql pagingSql, ComputeCount computeCount, String sql,
	        int totals, int pageSize) {
		if (null == pagingSql) {
			throw new IllegalArgumentException(
			        "Page pagingSql instance must not be null.");
		}

		if (pageSize <= 0) {
			throw new IllegalArgumentException(
			        "Page pageSize must greater than zero.");
		}

		if (null == sql || "".equals(sql)) {
			throw new IllegalArgumentException(
			        "Page sql must not be empty or null.");
		}

		if (null != computeCount) {
			this.computeCount = computeCount;
			totals = computeCount.getTotals(wrapperQueryCountSql(sql));
		}

		if (totals < 0) {
			throw new IllegalArgumentException(
			        "Page totals must greater than or equal to zero.");
		}

		this.pagingSql = pagingSql;
		this.sql = sql;
		this.totals = totals;
		this.pageSize = pageSize;
		this.computePages();
	}

	private void computePages() {
		this.pages = this.totals / this.pageSize;
		if (this.totals % this.pageSize > 0) {
			this.pages++;
		}
		if (this.pages == 0) {
			this.pages = 1;
		}
	}

	public String getPagingSql() {
		if (computeCount != null) {
			int totals = computeCount.getTotals(wrapperQueryCountSql(sql));
			return this.getPagingSql(totals);
		} else {
			return pagingSql.getPagingExecutableSql(this);
		}
	}

	public String getPagingSql(int totals) {
		if (totals < 0) {
			throw new IllegalArgumentException(
			        "Page totals must greater than or equal to zero.");
		}
		this.totals = totals;
		this.computePages();
		return pagingSql.getPagingExecutableSql(this);
	}

	public int getCurrentIndex() {
		return currentIndex;
	}

	public void setCurrentIndex(int currentIndex) {
		if (currentIndex <= 0) {
			currentIndex = 1;
		}
		if (currentIndex > this.pages) {
			currentIndex = this.pages;
		}
		this.currentIndex = currentIndex;
	}

	public String getSql() {
		return sql;
	}

	public int getPages() {
		return pages;
	}

	public int getPageSize() {
		return pageSize;
	}

	@Override
	public String toString() {
		return "Page [\ntotals = " + totals + ", currentIndex = "
		        + currentIndex + ", pageSize = " + pageSize + ", pages = "
		        + pages + ",\npagingSql = "
		        + pagingSql.getPagingExecutableSql(this) + ",\nsql = " + sql
		        + ",\ncountSql = " + wrapperQueryCountSql(sql) + "\n]";
	}

	private static String wrapperQueryCountSql(String sql) {
		return "select count(*) from (" + sql + ")";
	}

	/**
	 * Cpomute query record number by speacial sql.
	 * <p>
	 * Exampe
	 * 
	 * <pre>
	 * select count(*) from (sql) 
	 * return int type represent record number.
	 * </pre>
	 */
	public interface ComputeCount{

		int getTotals(String sql);
	}

	static class InnerComputeCount implements ComputeCount{

		private Connection conn;

		public InnerComputeCount(Connection conn) {
			this.conn = conn;
		}

		@Override
		public int getTotals(String sql) {
			PreparedStatement pstmt = null;
			ResultSet rs = null;
			try {
				pstmt = conn.prepareStatement(wrapperQueryCountSql(sql));
				rs = pstmt.executeQuery();
				if (rs.next()) {
					int totals = rs.getInt(1);
					return totals;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				try {
					if (null != rs) {
						rs.close();
					}
					if (null != pstmt) {
						pstmt.cancel();
					}
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			return 0;
		}
	}

	/**
	 * Throught Page instance create a paging sql by speacial database.
	 */
	public interface PagingSql{

		String getPagingExecutableSql(Page page);
	}

	/**
	 * MySQLPagingSql generator
	 */
	public static class MySQLPagingSql implements PagingSql{

		@Override
		public String getPagingExecutableSql(Page page) {
			int currentIndex = page.getCurrentIndex();
			int pageSize = page.getPageSize();
			int start = (currentIndex - 1) * pageSize;
			int end = currentIndex * pageSize;
			return "select * from (" + page.getSql() + ") limit " + start
			        + ", " + end;
		}

	}

	/**
	 * PostgresPagingSql generator
	 */
	public static class PostgresPagingSql implements PagingSql{

		@Override
		public String getPagingExecutableSql(Page page) {
			int currentIndex = page.getCurrentIndex();
			int pageSize = page.getPageSize();
			int limit = pageSize;
			int offset = (currentIndex - 1) * pageSize;
			return "select * from (" + page.getSql() + ") limit " + limit
			        + " offset " + offset;
		}
	}
}
